package cn.aezo.minions.utils;

import cn.aezo.core.entity.SqlRunnable;
import cn.aezo.core.entity.datasource.DBConfig;
import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;
import org.apache.ibatis.jdbc.SqlRunner;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.scripting.xmltags.DynamicSqlSource;
import org.apache.ibatis.scripting.xmltags.TextSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Created by smalle on 2017/9/8.
 * 基于mybatis的sql运行工具
 */
public class SqlHelper implements SqlRunnable {
    private static final String DRIVER = "driver";
    private static final String URL = "url";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";

    private DBConfig dbConfig;

    private SqlHelper(DBConfig dbConfig) {
        this.dbConfig = dbConfig;
    }

    /**
     * 获取一个sql运行器实例
     * @param dbConfig 数据库配置
     * @return
     */
    public static SqlHelper getInstance(DBConfig dbConfig) {
        return new SqlHelper(dbConfig);
    }

    /**
     * 基于mybatis运行sql
     *
     <pre>
     String sql = "SELECT * FROM user WHERE user_id=${id}";

     DataSourceConfig dataSourceConfig = new DataSourceConfig();
     dataSourceConfig.setDriverClass("com.mysql.jdbc.Driver");
     dataSourceConfig.setJdbcUrl("jdbc:mysql://localhost:3306/test");
     dataSourceConfig.setUsername("root");
     dataSourceConfig.setPassword("root");

     Map<String, Object> params = new HashMap<String, Object>();
     params.put("id", 2);

     List<Map<String, Object>> list = SqlRunnable.runSql(sql, params);
     </pre>
     * @param sql
     * @param params
     * @return
     */
    @Override
    public List<Map<String, Object>> runSql(String sql, Map<String, Object> params) {
        DataSource dataSource = buildDataSource(dbConfig);

        String runSql = buildSqlWithParams(dataSource, sql, params);
        String[] sqls = runSql.split(";");
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            SqlRunner runner = buildSqlRunner(conn);
            int sqlCount = sqls.length;
            if(sqlCount == 1) {
                return runner.selectAll(sqls[0], new Object[]{});
            } else {
                for (int i = 0; i < sqlCount-1; i++) {
                    runner.run(sqls[i]);
                }
                return runner.selectAll(sqls[sqlCount - 1], new Object[]{});
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
            return null;
        } finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<Map<String, Object>> runSql(String sql) {
        return runSql(sql, null);
    }

    /**
     * 参数绑定
     * @param dataSource
     * @param sql
     * @param params
     * @return
     */
    private static String buildSqlWithParams(DataSource dataSource, String sql, Map<String, Object> params) {
        Configuration configuration = buildConfiguration(dataSource);

        TextSqlNode node = new TextSqlNode(sql);

        DynamicSqlSource dynamicSqlSource = new DynamicSqlSource(configuration, node);

        BoundSql boundSql = dynamicSqlSource.getBoundSql(params);

        return boundSql.getSql();
    }

    /**
     * 绑定sql运行器
     * @param connection
     * @return
     */
    private static SqlRunner buildSqlRunner(Connection connection){
        return new SqlRunner(connection);
    }

    /**
     * 绑定数据源
     * @param dbConfig
     * @return
     */
    private static DataSource buildDataSource(DBConfig dbConfig) {
        Properties properties = new Properties();

        properties.setProperty(DRIVER, dbConfig.getDriverClass());
        properties.setProperty(URL, dbConfig.getJdbcUrl());
        properties.setProperty(USERNAME, dbConfig.getUsername());
        properties.setProperty(PASSWORD, dbConfig.getPassword());

        PooledDataSourceFactory pooledDataSourceFactory = new PooledDataSourceFactory();
        pooledDataSourceFactory.setProperties(properties);

        return pooledDataSourceFactory.getDataSource();
    }

    /**
     * 绑定配置
     * @param dataSource
     * @return
     */
    private static Configuration buildConfiguration(DataSource dataSource) {
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("development", transactionFactory, dataSource);

        return new Configuration(environment);
    }

    public DBConfig getDbConfig() {
        return dbConfig;
    }

    public void setDbConfig(DBConfig dbConfig) {
        this.dbConfig = dbConfig;
    }
}
